package com.zhenwei.zscm.service;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.zhenwei.zscm.common.base.RestMsg;
import com.zhenwei.zscm.common.base.RestResult;
import com.zhenwei.zscm.common.base.UserContext;
import com.zhenwei.zscm.common.excel.ExcelManager;
import com.zhenwei.zscm.common.excel.model.ExcelBaseModel;
import com.zhenwei.zscm.common.excel.model.OrderExcelModelExcel;
import com.zhenwei.zscm.common.excel.model.OrderExcelWriteModelExcel;
import com.zhenwei.zscm.common.exception.CustomException;
import com.zhenwei.zscm.common.utils.Linq;
import com.zhenwei.zscm.common.utils.Utils;
import com.zhenwei.zscm.mappings.DictMapping;
import com.zhenwei.zscm.mappings.FileMapping;
import com.zhenwei.zscm.mappings.ProductPartMapping;
import com.zhenwei.zscm.model.dto.dict.DictDTO;
import com.zhenwei.zscm.model.dto.file.FileDTO;
import com.zhenwei.zscm.model.dto.file.FileRelationDTO;
import com.zhenwei.zscm.model.dto.order.*;
import com.zhenwei.zscm.model.dto.productPart.ProductPartDTO;
import com.zhenwei.zscm.model.dto.user.UserDTO;
import com.zhenwei.zscm.model.enums.OrderStatusEnum;
import com.zhenwei.zscm.model.enums.RepairStateEnum;
import com.zhenwei.zscm.model.enums.TemplateEnum;
import com.zhenwei.zscm.model.vo.dict.DictVO;
import com.zhenwei.zscm.model.vo.order.*;
import com.zhenwei.zscm.model.vo.productPart.ProductPartVO;
import com.zhenwei.zscm.repo.*;
import lombok.AllArgsConstructor;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

@Service
@AllArgsConstructor
public class OrderService {
    private final OrderStatisticByDateRepo orderStatisticByDateRepo;
    private final FileRelationRepo fileRelationRepo;
    private SequencesRepo sequencesRepo;
    private OrderRepo orderRepo;
    private OrderProductRepo orderProductRepo;
    private OrderViewRepo orderViewRepo;
    private OrderProductCheckRepo orderProductCheckRepo;
    private OrderProductCheckPriceRepo orderProductCheckPriceRepo;
    private OrderProductRepairRepo orderProductRepairRepo;
    private FileRepo fileRepo;
    private DictRepo dictRepo;
    private ProductPartRepo productPartRepo;
    private ShippingViewRepo shippingViewRepo;
    private UserRepo userRepo;
    private LogRepo logRepo;
    private ExceptionLogRepo exceptionLogRepo;
    private OrderExportRepo orderExportRepo;
    private final String LOG_TYPE = "售后工单管理";
    private String _getOrderNumber() {
        long sequence = sequencesRepo.getOrderSequence();
        return "ZW" + DateTime.now().toString("yyyyMMdd") +  StrUtil.padPre(String.valueOf(sequence), 4, "0");
    }

    public RestResult getOrderNumber() {
        String orderNumber = _getOrderNumber();
        logRepo.createAsync(LOG_TYPE, "获取工单编号->"+orderNumber);
        return RestResult.success(orderNumber, 0L);
    }

    @Transactional(rollbackFor = {CustomException.class, Exception.class})
    public RestResult create(CreateOrderVO vo) {
        // 检查工单号是否存在
        boolean orderExists = orderRepo.checkOrderNumberExists(vo.getOrderNumber());
        if (orderExists) {
            return RestResult.fail(RestMsg.ORDER_NUMBER_EXISTS);
        }
        // 检查产品里是否存在重复的SN号
        boolean anyRepeat = Linq.anyRepeat(vo.getProducts(), CreateOrderProductVO::getProductSn);
        if (anyRepeat) {
            return RestResult.fail(RestMsg.PRODUCT_HAS_SAME_SN);
        }
        if(StrUtil.isBlank(vo.getComment())) {
            vo.setComment("");
        }
        // 保存表单主表
        boolean created = orderRepo.create(vo);
        if (!created) {
            return RestResult.fail(RestMsg.FAILURE);
        }
        // 保存产品表单
        boolean saved = orderProductRepo.createOrderProduct(vo.getGuid(), vo.getProducts());
        if (!saved) {
            throw new CustomException(RestMsg.FAILURE);
        }
        // 保存附件关联
        if(Linq.isNotEmpty(vo.getFiles())){
            saved = fileRelationRepo.saveRelation(vo.getGuid(), vo.getFiles());
            if (!saved) {
                throw new CustomException(RestMsg.FAILURE);
            }
        }
        logRepo.createAsync(LOG_TYPE, "创建工单->"+vo);
        return RestResult.success(vo.getGuid(), 0);
    }

    @Transactional(rollbackFor = {CustomException.class, Exception.class})
    public RestResult update(CreateOrderVO vo) {
        //
        OrderDTO order = orderRepo.get(vo.getGuid());
        if (order == null) {
            return RestResult.fail(RestMsg.ORDER_NOT_EXISTS);
        }
        // 只有管理员和本人能修改
//        UserDTO currentUser = UserContext.getCurrentUser();
//        if (UserContext.isNotAdmin() && !order.getCUserId().equals(currentUser.getGuid())) {
//            return RestResult.fail(RestMsg.NO_AUTH_UPDATE);
//        }
        // 检查订单状态，如果已经开始处理了，则不允许修改
//        if (UserContext.isNotAdmin() && !OrderStatusEnum.SUBMITTED.name().equals(order.getOrderStatus())) {
//            return RestResult.fail(RestMsg.ORDER_IS_PROCESSING);
//        }
        // 检查产品里是否存在重复的SN号
        boolean anyRepeat = Linq.anyRepeat(vo.getProducts(), CreateOrderProductVO::getProductSn);
        if (anyRepeat) {
            return RestResult.fail(RestMsg.PRODUCT_HAS_SAME_SN);
        }
        if(StrUtil.isBlank(vo.getComment())) {
            vo.setComment("");
        }
        boolean updated = orderRepo.update(vo);
        if (!updated) {
            return RestResult.fail(RestMsg.FAILURE);
        }
        // 先删除所有子表
        List<OrderProductDTO> oldProducts = orderProductRepo.getList(vo.getGuid());
        boolean isNeedResetOrderStatus = oldProducts.size() != vo.getProducts().size();
        // 判断出新增的数据，
        List<CreateOrderProductVO> filter = Linq.filter(vo.getProducts(), x -> !Linq.exists(oldProducts, o -> o.getProductSn().equals(x.getProductSn())));

//        boolean deleted = orderProductRepo.deleteOrderProduct(vo.getGuid());
//        if (!deleted) {
//            throw new CustomException(RestMsg.FAILURE);
//        }
        // 重新保存
        if(Linq.isNotEmpty(filter)) {
            boolean saved = orderProductRepo.createOrderProduct(vo.getGuid(), filter);
            if (!saved) {
                throw new CustomException(RestMsg.FAILURE);
            }
        }
        // 删除文件
        fileRelationRepo.delete(vo.getGuid());
        if(Linq.isNotEmpty(vo.getFiles())){
            var saved = fileRelationRepo.saveRelation(vo.getGuid(), vo.getFiles());
            if (!saved) {
                throw new CustomException(RestMsg.FAILURE);
            }
        }
        // 重置一下工单的状态
        if (isNeedResetOrderStatus) {
            orderRepo.updateStatus(vo.getGuid(), OrderStatusEnum.PROCESSING);
        }
        logRepo.createAsync(LOG_TYPE, "修改工单->"+vo);
        return RestResult.success(vo.getGuid(), 0);
    }

    @Transactional(rollbackFor = {CustomException.class, Exception.class})
    public RestResult delete(String orderId) {
        //
        OrderDTO order = orderRepo.get(orderId);
        if (order == null) {
            return RestResult.fail(RestMsg.ORDER_NOT_EXISTS);
        }
        // 只有管理员和本人能修改
        UserDTO currentUser = UserContext.getCurrentUser();
        if (UserContext.isNotAdmin() && !order.getCUserId().equals(currentUser.getGuid())) {
            return RestResult.fail(RestMsg.NO_AUTH_DELETE);
        }
        // 检查订单状态，如果已经开始处理了，则不允许修改
        if (UserContext.isNotAdmin() && !OrderStatusEnum.SUBMITTED.name().equals(order.getOrderStatus())) {
            return RestResult.fail(RestMsg.ORDER_IS_PROCESSING);
        }
        // 先删除主表
        boolean deleted = orderRepo.delete(orderId);
        if (!deleted) {
            throw new CustomException(RestMsg.FAILURE);
        }
        // 再删子表
        try {
            orderProductRepo.deleteOrderProduct(orderId);
            orderProductCheckRepo.delete(order.getGuid());
            orderProductCheckPriceRepo.delete(order.getGuid());
            orderProductRepairRepo.delete(order.getGuid());
        } catch (Exception e) {
            exceptionLogRepo.createAsync(LOG_TYPE, e.getMessage(), Arrays.toString(e.getStackTrace()));
            throw new CustomException(RestMsg.FAILURE);
        }

        logRepo.createAsync(LOG_TYPE, "删除工单->"+order.getOrderNumber());
        return RestResult.success();
    }

    @Transactional(rollbackFor = {CustomException.class, Exception.class})
    public RestResult invalid(String orderId) {
        //
        OrderDTO order = orderRepo.get(orderId);
        if (order == null) {
            return RestResult.fail(RestMsg.ORDER_NOT_EXISTS);
        }
        // 先删除主表
        boolean deleted = orderRepo.updateStatus(orderId, OrderStatusEnum.STOP);
        if (!deleted) {
            throw new CustomException(RestMsg.FAILURE);
        }
        logRepo.createAsync(LOG_TYPE, "作废工单->"+order.getOrderNumber());
        return RestResult.success();

    }
    public RestResult detail(String orderId) {
        //
        OrderDTO order = orderRepo.get(orderId);
        if (order == null) {
            return RestResult.error(RestMsg.ORDER_NOT_EXISTS);
        }
        order.setOrderStatusName(OrderStatusEnum.textOf(order.getOrderStatus()));
        var detail = new OrderDetailDTO();
        detail.setOrder(order);
        // 查询产品列表
        List<OrderProductDTO> orderProductList = orderProductRepo.getList(order.getGuid());
        detail.setProducts(orderProductList);
        List<OrderProductCheckDTO> checkList = orderProductCheckRepo.getList(order.getGuid());
        detail.setProductChecks(checkList);
        List<OrderProductCheckPriceDTO> priceList = orderProductCheckPriceRepo.list(order.getGuid());
        detail.setProductCheckPrices(priceList);
        List<OrderProductRepairDTO> repairList = orderProductRepairRepo.getList(order.getGuid());
        detail.setProductRepairs(repairList);
        List<FileRelationDTO> files = fileRelationRepo.getFiles(order.getGuid());
        if(Linq.isNotEmpty(files)){
            List<FileDTO> list = fileRepo.getList(Linq.select(files, FileRelationDTO::getFileId));
            detail.setFileList(Linq.mapList(list, FileMapping.INSTANCE::dto2Info));
        }

        return RestResult.success(detail, 0);
    }

    public RestResult page(QueryOrderVO query) {
        List<OrderViewDTO> list = orderViewRepo.page(query);
        return RestResult.success(list, query.getTotalCount());
    }

    public RestResult getProductDetail(String orderId, String productSn) {
        OrderProductDTO orderProductDTO = orderProductRepo.get(orderId, productSn);
        return RestResult.success(orderProductDTO, 0);
    }

    public RestResult getBaseInfo(String orderId) {
        OrderDTO orderDTO = orderRepo.get(orderId);
        return RestResult.success(orderDTO, 0);
    }

    public RestResult getProductSnRepairCount(String productSn) {
        Long productRepairCount = orderProductRepo.getProductRepairCount(productSn);
        return RestResult.success(productRepairCount, 0);
    }

    @Transactional(rollbackFor = {CustomException.class, Exception.class})
    public RestResult importData(String fileId) {
        FileDTO fileDTO = fileRepo.getOne(fileId);
        if(fileDTO == null) {
            return RestResult.fail(RestMsg.READ_FILE_NOT_EXISTS);
        }
        var excelManager = new ExcelManager<>(OrderExcelModelExcel.class);
        excelManager.open(fileDTO.getFilePath())
                .readSheet(0)
                .unmergeCell();
        var readModels = excelManager.getList();
        if(Linq.isEmpty(readModels)) {
            return RestResult.fail(RestMsg.READ_FILE_IS_EMPTY);
        }
        //
        List<OrderDTO> orderList = new ArrayList<>();
        List<OrderProductDTO> orderProductList = new ArrayList<>();
        List<OrderProductCheckDTO> orderProductCheckList = new ArrayList<>();
        List<OrderProductCheckPriceDTO> orderProductCheckPriceList = new ArrayList<>();
        List<OrderProductRepairDTO> orderProductRepairList = new ArrayList<>();

        List<DictDTO> customerList = dictRepo.getList("CUSTOMER");
        List<DictDTO> productList = dictRepo.getList("PRODUCT");
        List<DictDTO> warrantyList = dictRepo.getList("WARRANTY");
        List<DictDTO> repairSiteList = dictRepo.getList("REPAIR_SITE");
        List<DictDTO> payTypeList = dictRepo.getList("PAY_TYPE");
        List<DictDTO> invoiceTypeList = dictRepo.getList("INVOICE_TYPE");
        List<ProductPartDTO> productPartList = productPartRepo.getAll();

        readModels.forEach(model -> {
            String receiveNumber = model.getReceiveNumber();
            if (StrUtil.isBlank(receiveNumber)) {
                return;
            }
            boolean exists = Linq.exists(orderList, x -> StrUtil.isNotBlank(x.getReceiveNumber()) && x.getReceiveNumber().equals(receiveNumber));
            if(exists) {
                return;
            }
            List<OrderExcelModelExcel> filterList = Linq.filter(readModels,
                    x->StrUtil.isNotBlank(x.getReceiveNumber()) && x.getReceiveNumber().equals(receiveNumber));
            // 构造主表数据
            OrderDTO dto = resolveOrderDTO(receiveNumber, filterList, customerList);
            // 构造产品表数据
            List<OrderProductDTO> orderProducts = resolveOrderProductDTO(dto.getGuid(), filterList, productList);
            // 构造产品检测和维修数据，存放到orderProductCheckList、orderProductCheckPriceList、orderProductRepairList中
            resolveOrderProductCheckAndRepair(dto.getGuid(),
                    orderProductCheckList,
                    orderProductCheckPriceList,
                    orderProductRepairList,
                    filterList,
                    warrantyList,
                    repairSiteList,
                    productList,
                    payTypeList,
                    invoiceTypeList,
                    productPartList);
            orderList.add(dto);
            orderProductList.addAll(orderProducts);
        });
        // 更新一下所有工单产品的维修状态和维修数据表中的维修状态
        resolveOrderProductRepairState(orderList, orderProductList, orderProductCheckList, orderProductRepairList);
        // 持久化数据
        if(!orderRepo.saveBatch(orderList)){
            return RestResult.fail(RestMsg.IMPORT_FAILED);
        }
        if(!orderProductRepo.saveBatch(orderProductList)) {
            throw new CustomException(RestMsg.IMPORT_FAILED);
        }
        if(!orderProductCheckRepo.saveBatch(orderProductCheckList)) {
            throw new CustomException(RestMsg.IMPORT_FAILED);
        }
        if(!orderProductCheckPriceRepo.saveBatch(orderProductCheckPriceList)) {
            throw new CustomException(RestMsg.IMPORT_FAILED);
        }
        if(!orderProductRepairRepo.saveBatch(orderProductRepairList)) {
            throw new CustomException(RestMsg.IMPORT_FAILED);
        }

        logRepo.createAsync(LOG_TYPE, "导入工单数据->"+fileId);
        return RestResult.success();
    }
    /**
     * 处理订单主表数据
     * @param receiveNumber 接收快递单号
     * @param models 按接收快递单号筛选后的数据
     * @param customerList 客户字典列表
     * @return 订单主表DTO
     */
    private OrderDTO resolveOrderDTO(String receiveNumber, List<OrderExcelModelExcel> models, List<DictDTO> customerList) {
        var dto = new OrderDTO();
        dto.setGuid(IdUtil.simpleUUID());
        String orderNumber = _getOrderNumber();
        dto.setOrderNumber(orderNumber);
        {
            // 客户
            OrderExcelModelExcel f = Linq.findFirst(models, x -> StrUtil.isNotBlank(x.getCustomer()));
            String customer = f == null ? "" : Utils.emptyOrSelf(f.getCustomer());
            resolveCustomer(customerList, customer, dto);
        }
        {
            // 站点
            OrderExcelModelExcel f = Linq.findFirst(models, x -> StrUtil.isNotBlank(x.getSiteName()));
            dto.setSiteName(f == null ? "" : f.getSiteName());
        }
        {
            // 联系人
            OrderExcelModelExcel f = Linq.findFirst(models, x -> StrUtil.isNotBlank(x.getContactor()));
            dto.setContactor(f == null ? "" : f.getContactor());
        }
        {
            // 电话
            OrderExcelModelExcel f = Linq.findFirst(models, x -> StrUtil.isNotBlank(x.getTelephone()));
            dto.setTelephone(f == null ? "" : f.getTelephone());
        }
        {
            // 地址
            OrderExcelModelExcel f = Linq.findFirst(models, x -> StrUtil.isNotBlank(x.getAddress()));
            dto.setAddress(f == null ? "" : f.getAddress());
        }
        {
            OrderExcelModelExcel f = Linq.findFirst(models, x -> StrUtil.isNotBlank(x.getReceiveDate()));
            // 接收快递日期
            String receiveDate = Utils.emptyOrSelf(f == null ? "" : f.getReceiveDate());
            dto.setReceiveDate(StrUtil.isBlank(receiveDate) ? new Date() : DateUtil.parse(receiveDate).toJdkDate());
        }
        dto.setReceiveNumber(receiveNumber);
        {
            OrderExcelModelExcel f = Linq.findFirst(models, x -> StrUtil.isNotBlank(x.getCreateDate()));
            // 录入日期
            String createDate = Utils.emptyOrSelf(f == null ? "" : f.getCreateDate());
            dto.setCTime(StrUtil.isBlank(createDate) ? new Date() : DateUtil.parse(createDate).toJdkDate());
        }
        dto.setOrderStatus(OrderStatusEnum.PROCESSING.name());
        dto.setOrderStatusName(OrderStatusEnum.PROCESSING.getText());
        dto.setComment("");
        return dto;
    }
    /**
     * 构造产品数据
     * @param orderId 主表ID
     * @param filterList 按接收快递单号筛选出来的数据
     * @param productList 产品配置数据
     * @return 按SN去重后的所有产品数据
     */
    private List<OrderProductDTO> resolveOrderProductDTO(String orderId,
                                                         List<OrderExcelModelExcel> filterList,
                                                         List<DictDTO> productList) {
        List<OrderProductDTO> list = new ArrayList<>();

        filterList.forEach(model -> {
            var orderProductDTO = new OrderProductDTO();
            orderProductDTO.setGuid(IdUtil.simpleUUID());
            String productName = model.getProductName();
            if(StrUtil.isBlank(productName)) {
                return;
            }
            String productSn = model.getProductSn();
            if(StrUtil.isBlank(productSn)){
                return;
            }
            // 一个订单里不能有重复的
            if(Linq.exists(list, x->StrUtil.isNotBlank(x.getProductSn()) && x.getProductSn().equals(productSn))) {
                return;
            }
            orderProductDTO.setOrderId(orderId);
            orderProductDTO.setProductSn(productSn);
            {
                var dict = resolveProduct(productName, productList);
                if(dict == null) {
                    throw new CustomException(RestMsg.IMPORT_FAILED);
                }
                orderProductDTO.setProductName(dict.getName());
                orderProductDTO.setProductCode(dict.getCode());
            }
            {
                orderProductDTO.setRepairState(RepairStateEnum.CHECKING.getText());
                orderProductDTO.setRepairStateCode(RepairStateEnum.CHECKING.name());
            }
            Long productRepairCount = orderProductRepo.getProductRepairCount(model.getProductSn());
            orderProductDTO.setRepairCount(Math.toIntExact(productRepairCount));
            Date productShippingDate = shippingViewRepo.getProductShippingDate(model.getProductSn());
            orderProductDTO.setShippingDate(productShippingDate);
            list.add(orderProductDTO);
        });
        return list;
    }

    /**
     * 处理工单产品检测和维修数据
     * @param orderId 工单ID
     * @param checkList 所有检测数据集合，处理后的数据填充进该集合
     * @param priceList 所有检测配件数据集合，处理后的数据填充进该集合
     * @param repairList 所有维修数据集合，处理后的数据填充进该集合
     * @param filterList 按接收快递单号筛选后的数据集合
     * @param warrantyList 质保字典
     * @param repairSiteList 维修站点字典
     * @param productList 产品字典
     * @param payTypeList 付款方式字典
     * @param invoiceTypeList 发票类型字典
     * @param productPartList 产品配件字典
     */
    private void resolveOrderProductCheckAndRepair(String orderId,List<OrderProductCheckDTO> checkList,
                                                   List<OrderProductCheckPriceDTO> priceList,
                                                   List<OrderProductRepairDTO> repairList,
                                                   List<OrderExcelModelExcel> filterList,
                                                   List<DictDTO> warrantyList,
                                                   List<DictDTO> repairSiteList,
                                                   List<DictDTO> productList,
                                                   List<DictDTO> payTypeList,
                                                   List<DictDTO> invoiceTypeList,
                                                   List<ProductPartDTO> productPartList) {
        // 按产品sn分组处理
        List<String> snList = Linq.distinct(filterList, OrderExcelModelExcel::getProductSn);
        snList.forEach(sn -> {
            // 当前相同SN的所有数据
            List<OrderExcelModelExcel> models = Linq.filter(filterList, x -> x.getProductSn().equals(sn));
            // 如果有行填写了检测数据，以是否有检测工程师为依据
            List<OrderExcelModelExcel> checkModels = Linq.filter(models, x -> StrUtil.isNotBlank(x.getCheckEngineer()));
            // 1. 构建检测数据
            if (Linq.isNotEmpty(checkModels)) {
                OrderProductCheckDTO dto = resolveOrderProductCheck(orderId, sn, checkModels, warrantyList, repairSiteList);
                checkList.add(dto);
            }
            // 2. 构建检测配件数据
            checkModels.forEach(model -> {
                // 为了构建配件表数据
                var orderProductCheckPriceDTO = new OrderProductCheckPriceDTO();
                orderProductCheckPriceDTO.setGuid(IdUtil.simpleUUID());

                orderProductCheckPriceDTO.setOrderId(orderId);
                orderProductCheckPriceDTO.setProductSn(sn);
                orderProductCheckPriceDTO.setCount(StrUtil.isBlank(model.getCount()) ? 1 : Integer.parseInt(model.getCount()));
                {
                    // 处理产品配件信息
                    var product = Linq.findFirst(productList, x->x.getName().equals(model.getProductName()));
                    resolveProductPart(model.getPartName(), model.getCount(), model.getPrice(), productPartList, orderProductCheckPriceDTO, product);
                }
                priceList.add(orderProductCheckPriceDTO);
            });
            // 3. 构建维修数据
            var repairModels = Linq.filter(models, x->StrUtil.isNotBlank(x.getRepairEngineer()));
            if(Linq.isNotEmpty(repairModels)) {
                OrderProductRepairDTO repairDTO = resolveOrderProductRepair(orderId, sn, repairModels, payTypeList, invoiceTypeList);
                repairList.add(repairDTO);
            }
        });
    }

    /**
     * 处理产品检测数据
     * @param orderId 工单ID
     * @param sn SN
     * @param checkModels 按SN筛选后的，且检测工程师不为空的数据
     * @param warrantyList 质保状态字典
     * @param repairSiteList 维修站点字典
     * @return 产品检测DTO
     */
    private OrderProductCheckDTO resolveOrderProductCheck(String orderId, String sn, List<OrderExcelModelExcel> checkModels, List<DictDTO> warrantyList, List<DictDTO> repairSiteList) {
        var orderProductCheckDTO = new OrderProductCheckDTO();
        orderProductCheckDTO.setGuid(IdUtil.simpleUUID());
        orderProductCheckDTO.setOrderId(orderId);
        orderProductCheckDTO.setProductSn(sn);
        {
            OrderExcelModelExcel first = Linq.findFirst(checkModels, x -> StrUtil.isNotBlank(x.getQuestion()));
            orderProductCheckDTO.setQuestion(first != null ? first.getQuestion() : "");
        }
        {
            OrderExcelModelExcel first = Linq.findFirst(checkModels, x -> StrUtil.isNotBlank(x.getQuestionCheck()));
            orderProductCheckDTO.setQuestionCheck(first != null ? first.getQuestionCheck() : "");
        }
        {
            OrderExcelModelExcel first = Linq.findFirst(checkModels, x -> StrUtil.isNotBlank(x.getWarranty()));
            if(first == null){
                orderProductCheckDTO.setWarrantyCode("");
                orderProductCheckDTO.setWarranty("");
            } else {
                var dict = resolveWarranty(first.getWarranty(), warrantyList);
                if(dict == null) {
                    orderProductCheckDTO.setWarrantyCode("");
                    orderProductCheckDTO.setWarranty("");
                } else {
                    orderProductCheckDTO.setWarranty(dict.getName());
                    orderProductCheckDTO.setWarrantyCode(dict.getCode());
                }
            }
        }
        {
            OrderExcelModelExcel first = Linq.findFirst(checkModels, x -> StrUtil.isNotBlank(x.getRepairSite()));
            if(first == null){
                orderProductCheckDTO.setRepairSiteCode("");
                orderProductCheckDTO.setRepairSite("");
            } else {
                var dict = resolveRepairSite(first.getRepairSite(), repairSiteList);
                if(dict == null) {
                    orderProductCheckDTO.setRepairSiteCode("");
                    orderProductCheckDTO.setRepairSite("");
                } else {
                    orderProductCheckDTO.setRepairSite(dict.getName());
                    orderProductCheckDTO.setRepairSiteCode(dict.getCode());
                }
            }
        }
        {
            List<OrderExcelModelExcel> filter = Linq.filter(checkModels, x -> StrUtil.isNotBlank(x.getWorkHourPrice()));
            List<BigDecimal> workHourPrices = Linq.select(filter, x -> new BigDecimal(x.getWorkHourPrice()).setScale(2, RoundingMode.HALF_UP));
            BigDecimal sum = Linq.SumOfDecimal(workHourPrices);
            orderProductCheckDTO.setWorkHourPrice(sum);
        }
        {
            OrderExcelModelExcel first = Linq.findFirst(checkModels, x -> StrUtil.isNotBlank(x.getQuoteState()));
            orderProductCheckDTO.setQuoteState(first != null ? first.getQuoteState() : "否");
        }
        {
            OrderExcelModelExcel first = Linq.findFirst(checkModels, x -> StrUtil.isNotBlank(x.getQuoteDate()));
            orderProductCheckDTO.setQuoteDate(first == null || StrUtil.isBlank(first.getQuoteDate()) ? Utils.DefaultDate() : DateUtil.parse(first.getQuoteDate()).toJdkDate());
        }
        {
            OrderExcelModelExcel first = Linq.findFirst(checkModels, x -> StrUtil.isNotBlank(x.getQuoteConfirm()));
            orderProductCheckDTO.setIsQuoteConfirm(first != null && "是".equals(first.getQuoteState()));
        }
        {
            OrderExcelModelExcel first = Linq.findFirst(checkModels, x -> StrUtil.isNotBlank(x.getQuoteConfirmDate()));
            orderProductCheckDTO.setQuoteConfirmDate(first == null || StrUtil.isBlank(first.getQuoteConfirmDate()) ? Utils.DefaultDate() : DateUtil.parse(first.getQuoteConfirmDate()).toJdkDate());
        }
        {
            OrderExcelModelExcel first = Linq.findFirst(checkModels, x -> StrUtil.isNotBlank(x.getCheckEngineer()));
            var user = userRepo.getUserByName(first.getCheckEngineer());
            orderProductCheckDTO.setCUser(first.getCheckEngineer());
            orderProductCheckDTO.setCUserId(user == null ? "" : user.getGuid());
        }
        return orderProductCheckDTO;
    }

    /**
     * 处理产品维修数据
     * @param orderId 工单ID
     * @param sn 产品SN
     * @param filterList 通过SN筛选后的，且维修工程师字段不为空的数据集合
     * @param payTypeList 付款方式字典
     * @param invoiceTypeList 发票类型字典
     * @return 产品维修DTO
     */
    private OrderProductRepairDTO resolveOrderProductRepair(String orderId, String sn, List<OrderExcelModelExcel> filterList, List<DictDTO> payTypeList, List<DictDTO> invoiceTypeList) {
        var orderProductRepairDTO = new OrderProductRepairDTO();
        orderProductRepairDTO.setGuid(IdUtil.simpleUUID());
        orderProductRepairDTO.setOrderId(orderId);
        orderProductRepairDTO.setProductSn(sn);
        {
            var first = Linq.findFirst(filterList, x->StrUtil.isNotBlank(x.getPayState()));
            orderProductRepairDTO.setPayState(first == null || StrUtil.isBlank(first.getPayState()) ? "否" : first.getPayState());
        }
        {
            var first = Linq.findFirst(filterList, x->StrUtil.isNotBlank(x.getPayState()));
            orderProductRepairDTO.setPayDate(first == null || StrUtil.isBlank(first.getPayDate()) ? Utils.DefaultDate() : DateUtil.parse(first.getPayDate()).toJdkDate());
        }
        {
            var first = Linq.findFirst(filterList, x->StrUtil.isNotBlank(x.getPayType()));
            if(first == null) {
                orderProductRepairDTO.setPayType("");
                orderProductRepairDTO.setPayTypeCode("");
            } else {
                var dict = resolvePayType(first.getPayType(), payTypeList);
                if(dict == null) {
                    orderProductRepairDTO.setPayType("");
                    orderProductRepairDTO.setPayTypeCode("");
                } else {
                    orderProductRepairDTO.setPayType(dict.getName());
                    orderProductRepairDTO.setPayTypeCode(dict.getCode());
                }
            }
        }
        {
            var first = Linq.findFirst(filterList, x->StrUtil.isNotBlank(x.getIsInvoice()));
            orderProductRepairDTO.setIsInvoice(first != null && !StrUtil.isBlank(first.getIsInvoice()) && "是".equals(first.getIsInvoice()));
        }
        {
            var first = Linq.findFirst(filterList, x->StrUtil.isNotBlank(x.getInvoiceType()));
            if(first == null) {
                orderProductRepairDTO.setInvoiceType("");
                orderProductRepairDTO.setInvoiceTypeCode("");
            } else {
                var dict = resolveInvoiceType(first.getInvoiceType(), invoiceTypeList);
                if(dict == null) {
                    orderProductRepairDTO.setInvoiceType("");
                    orderProductRepairDTO.setInvoiceTypeCode("");
                } else {
                    orderProductRepairDTO.setInvoiceType(dict.getName());
                    orderProductRepairDTO.setInvoiceTypeCode(dict.getCode());
                }
            }
        }
        {
            var first = Linq.findFirst(filterList, x->StrUtil.isNotBlank(x.getInvoiceNumber()));
            orderProductRepairDTO.setInvoiceNumber(first == null || StrUtil.isBlank(first.getInvoiceNumber()) ? StrUtil.EMPTY : first.getInvoiceNumber());
        }
        {
            var first = Linq.findFirst(filterList, x->StrUtil.isNotBlank(x.getInvoiceDate()));
            orderProductRepairDTO.setInvoiceDate(first == null || StrUtil.isBlank(first.getInvoiceDate()) ? Utils.DefaultDate() : DateUtil.parse(first.getInvoiceDate()).toJdkDate());
        }
        {
            var first = Linq.findFirst(filterList, x->StrUtil.isNotBlank(x.getReturnDate()));
            orderProductRepairDTO.setReturnDate(first == null || StrUtil.isBlank(first.getReturnDate()) ? Utils.DefaultDate() : DateUtil.parse(first.getReturnDate()).toJdkDate());
        }
        {
            var first = Linq.findFirst(filterList, x->StrUtil.isNotBlank(x.getReturnNumber()));
            orderProductRepairDTO.setReturnNumber(first == null || StrUtil.isBlank(first.getReturnNumber()) ? StrUtil.EMPTY : first.getReturnNumber());
        }
        {
            orderProductRepairDTO.setRepairState(RepairStateEnum.CHECKING.getText());
            orderProductRepairDTO.setRepairStateCode(RepairStateEnum.CHECKING.name());
        }
        {
            var first = Linq.findFirst(filterList, x->StrUtil.isNotBlank(x.getRepairEngineer()));
            UserDTO user = userRepo.getUserByName(first.getRepairEngineer());
            orderProductRepairDTO.setCUser(first.getRepairEngineer());
            orderProductRepairDTO.setCUserId(user == null ? "" : user.getGuid());
        }
        return orderProductRepairDTO;
    }

    /**
     * 处理工单状态和产品维修状态
     * @param orderList 工单集合
     * @param orderProductList 产品集合
     * @param checkList 产品检测数据集合
     * @param repairList 产品维修数据集合
     */
    private void resolveOrderProductRepairState(
            List<OrderDTO> orderList,
            List<OrderProductDTO> orderProductList,
            List<OrderProductCheckDTO> checkList,
            List<OrderProductRepairDTO> repairList) {
        // 先处理工单产品的维修状态
        orderProductList.forEach(orderProduct->{
            RepairStateEnum state = RepairStateEnum.CHECKING;
            boolean isChecked = Linq.exists(checkList,
                    x->x.getProductSn().equals(orderProduct.getProductSn())
                                        && x.getOrderId().equals(orderProduct.getOrderId())
            );
            if(isChecked) {
                state = RepairStateEnum.SALE_PRICE;
            }
            boolean isQuoted = Linq.exists(checkList,
                    x->x.getProductSn().equals(orderProduct.getProductSn())
                            && x.getOrderId().equals(orderProduct.getOrderId())
                            && StrUtil.isNotBlank(x.getQuoteState())
                            && "是".equals(x.getQuoteState())
            );
            if(isQuoted) {
                state = RepairStateEnum.CONFIRMING;
            }
            boolean isQuoteConfirmed = Linq.exists(checkList,
                    x->x.getProductSn().equals(orderProduct.getProductSn())
                            && x.getOrderId().equals(orderProduct.getOrderId())
                            && x.getIsQuoteConfirm()
            );
            if(isQuoteConfirmed) {
                state = RepairStateEnum.REPAIRING;
            }
            boolean isRepaired =  Linq.exists(repairList,
                    x->x.getProductSn().equals(orderProduct.getProductSn())
                            && x.getOrderId().equals(orderProduct.getOrderId())
            );
            if(isRepaired) {
                state = RepairStateEnum.REPAIRED;
            }
            orderProduct.setRepairState(state.getText());
            orderProduct.setRepairStateCode(state.name());
            RepairStateEnum finalState = state;
            repairList.forEach(repair->{
                if(repair.getOrderId().equals(orderProduct.getOrderId()) && repair.getProductSn().equals(orderProduct.getProductSn())) {
                    repair.setRepairState(finalState.getText());
                    repair.setRepairStateCode(finalState.name());
                }

            });
        });
        // 处理工单状态
        orderList.forEach(order->{
           var isNotCompleted = Linq.exists(orderProductList, x->x.getOrderId().equals(order.getGuid())
           && !RepairStateEnum.REPAIRED.name().equals(x.getRepairStateCode())
                   );
           if(isNotCompleted) {
               order.setOrderStatus(OrderStatusEnum.PROCESSING.name());
               order.setOrderStatusName(OrderStatusEnum.PROCESSING.getText());
           } else {
               order.setOrderStatus(OrderStatusEnum.FINISHED.name());
               order.setOrderStatusName(OrderStatusEnum.FINISHED.getText());
           }
        });
    }

    /**
     * 处理客户名称
     * @param customerList 客户字典
     * @param customer 客户名称
     * @param dto 主表DTO引用
     */
    private void resolveCustomer(List<DictDTO> customerList, String customer, OrderDTO dto) {
        if(StrUtil.isBlank(customer)) {
            dto.setCustomerCode("");
            dto.setCustomerName("");
        } else {
            DictDTO dict = Linq.findFirst(customerList, x -> x.getName().equals(customer));
            if(dict != null) {
                dto.setCustomerName(dict.getName());
                dto.setCustomerCode(dict.getCode());
            } else {
                // 创建一下这个字典
                var vo = new DictVO();
                vo.setName(customer);
                vo.setCode(RandomUtil.randomString(6));
                vo.setDictType("CUSTOMER");
                if(dictRepo.addDict(vo)){
                    dto.setCustomerCode(vo.getCode());
                    dto.setCustomerName(vo.getName());
                    customerList.add(DictMapping.INSTANCE.vo2Dto(vo));
                }
            }
        }
    }

    /**
     * 处理质保状态名称
     * @param warranty 质保状态
     * @param warrantyList 质保状态字典
     * @return 质保状态为空则返回null；否则创建新字典或这返回字典
     */
    private DictDTO resolveWarranty(String warranty, List<DictDTO> warrantyList) {
        if(StrUtil.isBlank(warranty)) {
            return null;
        }
        var dict = Linq.findFirst(warrantyList, x->x.getName().equals(warranty));
        if(dict == null) {
            // 创建一下这个字典
            var vo = new DictVO();
            vo.setName(warranty);
            vo.setCode(RandomUtil.randomString(6));
            vo.setDictType("WARRANTY");
            if(dictRepo.addDict(vo)){
                warrantyList.add(DictMapping.INSTANCE.vo2Dto(vo));
                return DictMapping.INSTANCE.vo2Dto(vo);
            }
        }
        return dict;
    }

    /**
     * 处理维修站点名称
     * @param repairSite 维修站点
     * @param repairSiteList 维修站点列表
     * @return 维修站点为空则返回null；否则创建新字典或这返回字典
     */
    private DictDTO resolveRepairSite(String repairSite, List<DictDTO> repairSiteList) {
        if(StrUtil.isBlank(repairSite)) {
            return null;
        }
        var dict = Linq.findFirst(repairSiteList, x->x.getName().equals(repairSite));
        if(dict == null) {
            // 创建一下这个字典
            var vo = new DictVO();
            vo.setName(repairSite);
            vo.setCode(RandomUtil.randomString(6));
            vo.setDictType("REPAIR_SITE");
            if(dictRepo.addDict(vo)){
                repairSiteList.add(DictMapping.INSTANCE.vo2Dto(vo));
                return DictMapping.INSTANCE.vo2Dto(vo);
            }
        }
        return dict;
    }
    /**
     * 处理产品名称
     * @param productName 产品名称
     * @param productList 产品字典清单
     * @return 如果产品名称为空，则返回null；否则创建字典或返回字典
     */
    private DictDTO resolveProduct(String productName, List<DictDTO> productList) {
        if(StrUtil.isBlank(productName)) {
            return null;
        }
        var dict = Linq.findFirst(productList, x->x.getName().equals(productName));
        if(dict == null) {
            // 创建一下这个字典
            var vo = new DictVO();
            vo.setName(productName);
            vo.setCode(RandomUtil.randomString(6));
            vo.setDictType("PRODUCT");
            if(dictRepo.addDict(vo)){
                productList.add(DictMapping.INSTANCE.vo2Dto(vo));
                return DictMapping.INSTANCE.vo2Dto(vo);
            }
        }
        return dict;
    }

    /**
     * 处理发票类型名称
     * @param invoiceType 发票类型
     * @param invoiceTypeList 发票类型字典
     * @return 如果发票类型为空，则返回null；否则创建字典或返回字典
     */
    private DictDTO resolveInvoiceType(String invoiceType, List<DictDTO> invoiceTypeList) {
        if(StrUtil.isBlank(invoiceType)) {
            return null;
        }
        var dict = Linq.findFirst(invoiceTypeList, x->x.getName().equals(invoiceType));
        if(dict == null) {
            // 创建一下这个字典
            var vo = new DictVO();
            vo.setName(invoiceType);
            vo.setCode(RandomUtil.randomString(6));
            vo.setDictType("INVOICE_TYPE");
            if(dictRepo.addDict(vo)){
                invoiceTypeList.add(DictMapping.INSTANCE.vo2Dto(vo));
                return DictMapping.INSTANCE.vo2Dto(vo);
            }
        }
        return dict;
    }

    /**
     * 处理付款方式名称
     * @param payType 付款方式
     * @param payTypeList 付款方式字典
     * @return 如果付款方式为空，则返回null；否则创建字典或返回字典
     */
    private DictDTO resolvePayType(String payType, List<DictDTO> payTypeList) {
        if(StrUtil.isBlank(payType)) {
            return null;
        }
        var dict = Linq.findFirst(payTypeList, x->x.getName().equals(payType));
        if(dict == null) {
            // 创建一下这个字典
            var vo = new DictVO();
            vo.setName(payType);
            vo.setCode(RandomUtil.randomString(6));
            vo.setDictType("PAY_TYPE");
            if(dictRepo.addDict(vo)){
                payTypeList.add(DictMapping.INSTANCE.vo2Dto(vo));
                return DictMapping.INSTANCE.vo2Dto(vo);
            }
        }
        return dict;
    }
    /**
     * 处理产品配件数据
     * @param partName 配件名称
     * @param countStr 配件使用数量
     * @param priceStr 配件费用合计
     * @param productPartList 产品配件清单
     * @param orderProductCheckPriceDTO 工单产品维修配件DTO引用
     * @param product 产品字典
     */
    private void resolveProductPart(String partName, String countStr, String priceStr, List<ProductPartDTO> productPartList, OrderProductCheckPriceDTO orderProductCheckPriceDTO, DictDTO product) {
        var part = Linq.findFirst(productPartList, x->product.getCode().equals(x.getProductCode()) && x.getPartName().equals(partName));
        BigDecimal price = new BigDecimal(priceStr).setScale(2, RoundingMode.HALF_UP);
        var count = StrUtil.isBlank(countStr) ? 1 : Integer.parseInt(countStr);
        // 计算一下单价
        BigDecimal divide = price.divide(new BigDecimal(count), 2, RoundingMode.HALF_UP);
        if(part == null) {
            // 创建一下这个配件信息
            var partVO = new ProductPartVO();
            partVO.setGuid(IdUtil.simpleUUID());
            partVO.setPartName(partName);
            partVO.setProductCode(product.getCode());
            partVO.setProductName(product.getName());
            partVO.setPrice(divide);
            if(productPartRepo.create(partVO)) {
                part = ProductPartMapping.INSTANCE.vo2Dto(partVO);
                productPartList.add(part);
            }
        }
        if (part != null) {
            orderProductCheckPriceDTO.setProductPartId(part.getGuid());
            orderProductCheckPriceDTO.setProductPartName(part.getPartName());
            orderProductCheckPriceDTO.setPrice(price);
            orderProductCheckPriceDTO.setTotalPrice(price);
        } else{
            orderProductCheckPriceDTO.setProductPartId("");
            orderProductCheckPriceDTO.setProductPartName("");
            orderProductCheckPriceDTO.setPrice(BigDecimal.valueOf(0));
            orderProductCheckPriceDTO.setTotalPrice(BigDecimal.valueOf(0));
        }
    }

    /**
     * 导出数据
     * @param vo 参数
     * @return RestResult
     */
    public RestResult export(ExportVO vo) {
        if(Linq.isEmpty(vo.getSelectedIds()) && vo.getQuery() == null) {
            return RestResult.fail(RestMsg.EXPORT_FAILED);
        }
        List<OrderExportDTO> details;
        // 导出指定数据
        if(Linq.isNotEmpty(vo.getSelectedIds())) {
            details= orderExportRepo.getAll(vo.getSelectedIds());
        } else {
            vo.getQuery().setPageSize(9999999);
            List<OrderViewDTO> page = orderViewRepo.page(vo.getQuery());
            details = orderExportRepo.getAll(Linq.select(page, OrderViewDTO::getGuid));
        }
        if(Linq.isEmpty(details)) {
            return RestResult.fail(RestMsg.NO_DATA_TO_EXPORT);
        }
        var data = new ArrayList<OrderExcelWriteModelExcel>();
        var bgColors = new HashMap<Integer, IndexedColors>();
        AtomicInteger i = new AtomicInteger(1);
        details.forEach(d -> {
            var model = new OrderExcelWriteModelExcel();
            model.setOrderNo(d.getOrderNumber());
            model.setNo(i.get()+"");
            model.setCUser(d.getCUser());
            model.setCTime(resolveDefaultDate(DateUtil.format(d.getCTime(), "yyyy-MM-dd")));
            model.setCustomer(d.getCustomerName());
            model.setSiteName(d.getSiteName());
            model.setContactor(d.getContactor());
            model.setTelephone(d.getTelephone());
            model.setAddress(d.getAddress());
            model.setReceiveDate(resolveDefaultDate(DateUtil.format(d.getReceiveDate(), "yyyy-MM-dd")));
            model.setReceiveNumber(d.getReceiveNumber());
            model.setProductName(d.getProductName());
            model.setProductSn(d.getProductSn());
            model.setCheckEngineer(d.getCheckEngineer());
            model.setQuestion(d.getQuestion());
            model.setQuestionCheck(d.getQuestionCheck());
            model.setWarranty(d.getWarranty());
            model.setRepairSite(d.getRepairSite());
            model.setPartName(d.getProductPartName());
            model.setCount(d.getCount().toString());
            model.setRepairState(d.getRepairState());
            model.setTotalPrice(d.getTotalPrice().toString());
            model.setWorkHourPrice(d.getWorkHourPrice().toString());
            model.setQuoteState(d.getQuoteState());
            model.setQuoteDate(resolveDefaultDate(DateUtil.format(d.getQuoteDate(), "yyyy-MM-dd")));
            model.setIsQuoteConfirm(d.getIsQuoteConfirm() ? "是" : "否");
            model.setQuoteConfirmDate(resolveDefaultDate(DateUtil.format(d.getQuoteConfirmDate(), "yyyy-MM-dd")));
            model.setRepairEngineer(d.getRepairEngineer());
            model.setPayState(d.getPayState());
            model.setPayType(d.getPayType());
            model.setPayDate(resolveDefaultDate(DateUtil.format(d.getPayDate(), "yyyy-MM-dd")));
            model.setIsInvoice(d.getIsInvoice() ? "是" : "否");
            model.setInvoiceType(d.getInvoiceType());
            model.setInvoiceDate(resolveDefaultDate(DateUtil.format(d.getInvoiceDate(), "yyyy-MM-dd")));
            model.setInvoiceNumber(d.getInvoiceNumber());
            model.setReturnDate(resolveDefaultDate(DateUtil.format(d.getReturnDate(), "yyyy-MM-dd")));
            model.setReturnNumber(d.getReturnNumber());
            model.setComment(d.getComment());
            data.add(model);
            i.getAndIncrement();
        });

        // 计算需要合并的区域
        List<String> distinct = new ArrayList<>();
        List<CellRangeAddress> cellRangeAddresses = new ArrayList<>();
        IndexedColors prevColor = IndexedColors.TAN;
        for (int j = 1; j <= data.size(); j++) {
            var model = data.get(j - 1);
            var orderNo = model.getOrderNo();
            // 如果已经处理过合并的工单，不重复处理
            if (Linq.exists(distinct, x -> x.equals(orderNo))) {
                bgColors.put(j, prevColor);
                continue;
            }
            prevColor = prevColor == IndexedColors.TAN ? IndexedColors.GREY_25_PERCENT : IndexedColors.TAN;
            bgColors.put(j, prevColor);
            // 筛选出相同接收单的数据
            List<OrderExcelWriteModelExcel> sameRows = Linq.filter(data, x -> x.getOrderNo().equals(orderNo));
            // 如果只有一行数据，不合并
            if(sameRows.size() == 1) {
                continue;
            }
            // 工单信息列，0-9
            var orderDataColumn = 10;
            // 合并工单信息，按接收快递单号合并
            for (int k = 1; k < orderDataColumn; k++) {
                var address = new CellRangeAddress(j, j + sameRows.size() - 1, k, k);
                cellRangeAddresses.add(address);
            }
            {
                // 以及最后一列，备注，37列
                var orderCommentColumn = 36;
                var address = new CellRangeAddress(j, j + sameRows.size() - 1, orderCommentColumn, orderCommentColumn);
                cellRangeAddresses.add(address);
            }

            var distinctSn = new ArrayList<String>();
            // 循环同一订单里的产品sn
            for (int k = 0; k < sameRows.size(); k++) {
                var m = sameRows.get(k);
                var sn = m.getProductSn();
                // 读取相同sn的数据
                List<OrderExcelWriteModelExcel> sameSn = Linq.filter(sameRows, x -> x.getProductSn().equals(sn));
                // 如果sn已经处理过，不合并
                if(Linq.exists(distinctSn, x->x.equals(sn))) {
                    continue;
                }
                distinctSn.add(sn);
                // 如果sn只有一行，说明只有一个配件，不合并
                if(sameSn.size() == 1) {
                    continue;
                }
                // 合并工单产品信息，按相同的SN号合并
                for (int l = 10; l < 36; l++) {
                    // 配件数据不合并
                    if (l == 17 || l == 18 || l == 20 ) continue;
                    var address = new CellRangeAddress(j+k, j+k + sameSn.size() - 1, l, l);
                    cellRangeAddresses.add(address);
                }
            }
            distinct.add(orderNo);
        }
        String exportFileName = String.format("工单明细导出%s.xlsx", DateUtil.format(new Date(), "yyyy_MM_dd_HH_mm"));
        var excelManage = new ExcelManager<>(OrderExcelWriteModelExcel.class);
        excelManage
                .useBgColorMap(bgColors)
                .useTemplate(TemplateEnum.ORDER_EXPORT, exportFileName)
                .writeSheetWithMergeCell(data, 0, "售后工单",cellRangeAddresses);
        // 保存文件数据
        var dto = new FileDTO();
        dto.setFileName(excelManage.getFile().getName());
        dto.setFilePath(excelManage.getFile().getAbsolutePath());
        dto.setFileSize(excelManage.getFile().length());
        dto.setFileType(getFileExtension(excelManage.getFile().getName()));
        var saved = fileRepo.Save(dto);
        if(saved) {
            logRepo.createAsync(LOG_TYPE, "导出工单数据，检索条件：" + JSONUtil.toJsonStr(vo));
        }
        return RestResult.success(saved ? dto.getGuid() : "", 0);
    }
    private String resolveDefaultDate(String dateStr) {
        if("1900-01-01".equals(dateStr)) {
            return "";
        }
        return dateStr;
    }
    private String getFileExtension(String filename) {
        if (filename == null || filename.isEmpty()) {
            return "";
        }
        int lastDotIndex = filename.lastIndexOf(".");
        if (lastDotIndex == -1 || lastDotIndex == filename.length() - 1) {
            return "";
        }
        return filename.substring(lastDotIndex);
    }

    public RestResult generateReport(String orderId) {
        var dto = orderRepo.get(orderId);
        if(dto == null) {
            return RestResult.fail(RestMsg.ORDER_NOT_EXISTS);
        }
        var main = new HashMap<String, String>();
        main.put("orderNumber", dto.getOrderNumber());
        main.put("customer", dto.getCustomerName());
        main.put("contactor", dto.getContactor());
        main.put("telephone", dto.getTelephone());
        main.put("address", dto.getAddress());
        List<OrderProductDTO> products = orderProductRepo.getList(orderId);
        List<OrderProductCheckDTO> checkList = orderProductCheckRepo.getList(orderId);
        List<OrderProductCheckPriceDTO> priceList = orderProductCheckPriceRepo.list(orderId);
        var list = new ArrayList<HashMap<String, String>>();
        List<String> snList = Linq.distinct(products, OrderProductDTO::getProductSn);
        AtomicInteger snCount = new AtomicInteger();
        snList.forEach(sn->{
            OrderProductCheckDTO check = Linq.findFirst(checkList, x -> x.getProductSn().equals(sn));
            if(check == null) {
                return;
            }
            var first = Linq.findFirst(products, x -> x.getProductSn().equals(sn));
            snCount.getAndIncrement();
            // 筛选配件费用数据
            List<OrderProductCheckPriceDTO> filter = Linq.filter(priceList, x -> x.getProductSn().equals(sn));
            // 如果没有配件费，只添加工时费，配件费用为0
            if(Linq.isEmpty(filter)) {
                list.add(new HashMap<>(){
                    {
                        put("productSn", sn);
                        put("workHourPrice", check.getWorkHourPrice().toString());
                        put("totalPrice", "0");
                        put("warranty", check.getWarranty());
                        put("questionCheck", check.getQuestionCheck());
                        put("partName", "");
//                        put("comment", StrUtil.isBlank(first.getComment()) ? "" : first.getComment());
                        put("comment", "");
                    }
                });
            } else {
                filter.forEach(p-> list.add(new HashMap<>(){
                    {
                        put("productSn", sn);
                        put("workHourPrice", check.getWorkHourPrice().toString());
                        put("totalPrice", p.getTotalPrice().toString());
                        put("warranty", check.getWarranty());
                        put("questionCheck", check.getQuestionCheck());
                        put("partName", p.getProductPartName());
//                        put("comment", StrUtil.isBlank(first.getComment()) ? "" : first.getComment());
                        put("comment", "");
                    }
                }));
            }
        });
        // 计算合并区域
        List<CellRangeAddress> cellRangeAddresses = new ArrayList<>();
        List<String> distinctSn = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            var map = list.get(i);
            String sn = map.get("productSn");
            if(distinctSn.contains(sn)) {
               continue;
            }
            distinctSn.add(sn);
            // filter的size就是要合并的行数
            List<HashMap<String, String>> filter = Linq.filter(list, x -> x.containsValue(sn));
            if(filter.size() <= 1) {
                continue;
            }
            for (int j = 0; j < 7; j++) {
                if (j == 2 || j == 5) continue;
                cellRangeAddresses.add(new CellRangeAddress(9 + i, i + filter.size() + 8, j, j));
            }
        }

        BigDecimal workHourPrice = Linq.SumOfDecimal(Linq.select(checkList, OrderProductCheckDTO::getWorkHourPrice));
        BigDecimal partPrice = Linq.SumOfDecimal(Linq.select(priceList, OrderProductCheckPriceDTO::getTotalPrice));
        main.put("totalCount", String.valueOf(snCount));
        main.put("totalPrice", workHourPrice.add(partPrice).toString());
        var excelManager = new ExcelManager<>(ExcelBaseModel.class);
        var fileName = "设备维修检测报告("+dto.getOrderNumber()+").xlsx";
        excelManager.useTemplate(TemplateEnum.PRODUCT_CHECK, fileName)
                .fill(cellRangeAddresses, main, list);
        // 保存文件数据
        var file = new FileDTO();
        file.setFileName(excelManager.getFile().getName());
        file.setFilePath(excelManager.getFile().getAbsolutePath());
        file.setFileSize(excelManager.getFile().length());
        file.setFileType(getFileExtension(excelManager.getFile().getName()));
        var saved = fileRepo.Save(file);
        if(saved) {
            logRepo.createAsync(LOG_TYPE, "生成检测报告单：【"+dto.getOrderNumber()+"】");
        }
        return RestResult.success(saved ? file.getGuid() : "", 0);
    }

    public RestResult dateOrderStatistic(QueryOrderStatisticVO query) {
        List<OrderStatisticByDateDTO> list = orderStatisticByDateRepo.dateOrderStatistic(query.getStartDate(), query.getEndDate());
        return RestResult.success(list, list.size());
    }

    public RestResult countByState(){
        List<?> objects = orderRepo.countByState();
        return RestResult.success(objects, objects.size());
    }
    public RestResult countByProductRepairState(){
        List<?> objects = orderRepo.countByProductRepairState();
        return RestResult.success(objects, objects.size());
    }

    public RestResult customerSearch(String searchKey) {
        if(StrUtil.isBlank(searchKey)) {
            return RestResult.fail("请输入查询参数");
        }
        searchKey = searchKey.replace("；",";");
        List<String> keys = StrUtil.split(searchKey, ";");
        var result = orderRepo.customerSearch(keys);
        logRepo.createAsync(LOG_TYPE, "客户查询工单：" + searchKey + "，查询结果：" + JSONUtil.toJsonStr(result));
        return RestResult.success(result, 0);
    }

    public RestResult deleteProduct(String orderId, String productSn) {
        var order =  orderRepo.get(orderId);
        if(order == null) {
            return RestResult.fail(RestMsg.ORDER_NOT_EXISTS);
        }
        boolean deleted = orderProductRepo.delete(orderId, productSn);
        if(!deleted) {
            return RestResult.fail(RestMsg.ORDER_PRODUCT_DELETE_FAILED);
        }
        logRepo.createAsync(LOG_TYPE, "工单【"+order.getOrderNumber()+"】删除了产品: 【"+productSn+"】");
        return RestResult.success();
    }
}
